#!/usr/bin/python
#coding=utf-8
from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
from common import df_operator as op, db_utils as util
import sys

dtypedict = {'ACCOUNT': VARCHAR,
            'FACTORY': VARCHAR,'COST_CENTER': VARCHAR,'UNIT': VARCHAR,'TEAM': VARCHAR,
            'SHIFT': VARCHAR,'WORK_TIME': VARCHAR,'MAT_NO': VARCHAR,'IN_MAT_NO': VARCHAR,
            'EDITION': VARCHAR,'EDITION_NAME': VARCHAR,'EDITION_UNIT': VARCHAR,
            'EDITION_VALUE_FACT': DECIMAL,'EDITION_VALUE_PLAN': DECIMAL,'EDITION_VALUE_GOAL': DECIMAL,
            'EDITION_VALUE_DIFF': DECIMAL,'LIMIT_UP': DECIMAL,'LIMIT_DOWN': DECIMAL,
            'REC_CREATOR': VARCHAR, 'REC_CREATE_TIME': VARCHAR, 'REC_REVISOR': VARCHAR,'REC_REVISOR_TIME': VARCHAR
            }

def MO0101A(cost_center,unit_code,start_time,end_time,edition):
    # 查询配置表，获取配置条件
    sql = """select CODE,CODE_NAME,CODE_UNIT,TYPE,CAL_COLUMN_1,SOURCE_TABLE_1,CAL_WHERE_1,CAL_COLUMN_2,SOURCE_TABLE_2,CAL_WHERE_2,SIZE_TYPE,SORT,FIELD1,FIELD2,DATA_BASE_1,DATA_BASE_2 from BGTARSSI00.T_DWD_WH_CB00SI_MO0101A WHERE COST_CENTER='MIDE'  and CODE = '"""+edition+"""' """
    df = pd.read_sql(sql, conn_rds)
    # 读取这段时间的基础信息
    sql1 = """SELECT 
                    ACCOUNT,COST_CENTER,UNIT_CODE,TEAM,SHIFT,WORK_DATE,MAT_NO,IN_MAT_NO,ST_NO,
                    THICK_COD,WIDTH_COD,IN_WIDTH_COD,IN_THICK_COD
                FROM BGTARSSI00.T_DWD_FACT_CB00SI_CD0201
                WHERE ACCOUNT = '1001' AND COST_CENTER =  '""" + cost_center + """' AND UNIT_CODE= '""" + unit_code + """'
                AND WORK_DATE BETWEEN '""" + str(start_time) + """' AND '""" + str(end_time) + """'
            """
    df_base = util.query(conn=conn_rds, sql=sql1)
    # 按照卷号遍历
    for info in zip(df_base['account'], df_base['cost_center'], df_base['unit_code'], df_base['team'], df_base['shift'],
                    df_base['work_date'], df_base['mat_no'], df_base['in_mat_no'],
                    df_base["st_no"], df_base["thick_cod"], df_base["width_cod"], df_base["in_width_cod"],
                    df_base["in_thick_cod"]):
        # 遍历动因
        for tup in zip(df['code'], df['code_name'], df['code_unit'], df['cal_column_1'], df['source_table_1'],
                       df['cal_where_1'], df['field1'], df['cal_column_2'], df['source_table_2'], df['cal_where_2'],
                       df['field2'], df['size_type'], df['data_base_1'], df['data_base_2']):
            cal_where_1 = op.replaceColumn(str(tup[5]), ["P_MAT_NO", "P_IN_MAT_NO", "P_COST_CENTER"], [info[6], info[7],cost_center])
            print(cal_where_1)
            sql2 = """
                    select '""" + info[0] + """' AS ACCOUNT,
                        '""" + info[1] + """' AS COST_CENTER,
                        '""" + info[2] + """' AS UNIT,
                        '""" + info[3] + """' AS TEAM,
                        '""" + info[4] + """' AS SHIFT,
                        '""" + info[5] + """' AS WORK_TIME,
                        '""" + info[6] + """' AS MAT_NO,
                        '""" + info[7] + """' AS IN_MAT_NO,
                        '""" + tup[0] + """' AS EDITION,
                        '""" + tup[1] + """' AS EDITION_NAME,
                        '""" + tup[2] + """' AS EDITION_UNIT,
                        """ + tup[3] + """ AS EDITION_VALUE_FACT,
                        'BGRAGGCB' AS REC_CREATOR,
                        TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                        '' AS REC_REVISOR,
                        TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                    FROM """ + tup[4] + """ WHERE """ + cal_where_1 + """
                    """ + tup[6] + """
                """
            print(sql2)
            df_fact = util.query(conn=conn_rds if tup[12] == "RDS" else conn_db2, sql=sql2)
            # 取目标值
            cal_where_2 = op.replaceColumn(str(tup[9]),
                                           ["P_COST_CENTER", "P_UNIT", "P_TEAM", "P_SHIFT", "P_WORK_DATE", "P_MAT_NO",
                                            "P_IN_MAT_NO", "P_ST_NO", "P_THICK_COD", "P_WIDTH_COD", "P_IN_THICK_COD"],
                                           [info[1], info[2], info[3], info[4], info[5], info[6], info[7], info[8],
                                            info[9], info[10], info[11], info[12]])

            sql_aim = """
                                SELECT '""" + info[0] + """' AS cost_center_2,
                                '""" + info[1] + """' AS unit_code ,
                                '""" + info[4] + """' AS work_date ,
                                '""" + info[5] + """' AS mat_no_2 ,
                                '""" + info[6] + """' AS in_mat_no_2 ,
                                '""" + tup[0] + """'  AS edition_2,
                                """ + tup[7] + """ 
                                FROM """ + tup[8] + """ where """ + cal_where_2 + tup[10] + """
                            """
            df_aim = util.query(conn=conn_rds if tup[13] == "RDS" else conn_db2, sql=sql_aim)
            df_result = op.merge_df(df_fact, df_aim,
                                    [["mat_no", "in_mat_no", "edition"], ["mat_no_2", "in_mat_no_2", "edition_2"]],
                                    how="left")
            df_result = df_result[['account', 'cost_center', 'unit', 'team', 'shift', 'work_time'
                , 'mat_no', 'in_mat_no', 'edition', 'edition_name', 'edition_unit', 'edition_value_fact',
                                   'edition_value_goal'
                , 'limit_up', 'limit_down', 'rec_creator', 'rec_revisor', 'rec_revisor_time', 'rec_create_time']]
            util.insert(df_result, conn_rds, "T_DWD_FACT_CB00SI_MO0101", "BGTARSSI00", dtypedict)

def MO0101B(cost_center,unit_code,start_time,end_time,edition):
    # 查询配置表，获取配置条件
    sql = """select edition,sql as sql_m,SIZE_TYPE from BGTARSSI00.T_DWD_WH_CB00SI_MO0101B WHERE COST_CENTER='MIDE' and edition = """+edition+""" """
    df = pd.read_sql(sql, conn_rds)
    # 遍历动因
    for tup in zip(df['edition'], df['sql_m'], df['size_type']):
        sql_m = op.replaceColumn(str(tup[1]), ["P_DATA_TYPE", "P_UNIT", "P_COST_CENTER", "P_ACCOUNT", "P_START_TIME", "P_END_TIME", "P_SIZE_TYPE"],
                                       ["0", unit_code,cost_center,"1001",start_time,end_time,str(tup[2])])
        df_result = util.query(conn_db2, sql=sql_m)
        util.insert(df_result, conn_rds, "T_DWD_FACT_CB00SI_MO0101", "BGTARSSI00", dtypedict)



def main(cost_center,unit_code,start_time,end_time):
    #先删除这段时间的数据
    del_sql = """delete from BGTARSSI00.T_DWD_FACT_CB00SI_MO0101 WHERE COST_CENTER='MIDE' and WORK_TIME BETWEEN '""" + str(start_time) + """' AND '""" + str(end_time) + """' AND EDITION = 'PRE_UNIT_CODE'"""
    conn_rds.execute(del_sql)
    MO0101A(cost_center,unit_code,start_time,end_time,"PRE_UNIT_CODE")
    # MO0101B(cost_center, unit_code, start_time, end_time)

if __name__ == '__main__':
    '''先读配置表
      再获取RDS连接
    '''
    now_time = datetime.datetime.now()
    # start_time = (datetime.datetime.now() - datetime.timedelta(minutes=360)).strftime("%Y%m%tmp_dict%H%M%S")
    # end_time = datetime.datetime.now().strftime("%Y%m%tmp_dict%H%M%S")
    start_time = '20210329200000'
    end_time = '20210330210000'
    global conn_db2, conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')

    main("MIDE","Q518",start_time,end_time)
    conn_rds.close()
    conn_db2.close()
    sys.exit()